import pymysql
#连接数据库
class Cmysql(object):
    def __init__(self,host,port,db,user,passwd,charset='utf8'):
        self.host=host
        self.port=port
        self.db=db
        self.user=user
        self.passwd=passwd
        self.charset=charset

    def connect(self):
        self.conn=pymysql.connect(host=self.host,port=self.port,db=self.db,user=self.user,passwd=self.passwd,
                                  charset=self.charset)
        self.cursor=self.conn.cursor()

    def close(self):
        self.cursor.close()
        self.conn.close()

    def get_all(self,sql,params=()):
        list=()
        try:
            self.connect()
            self.cursor.execute(sql,params)
            list=self.cursor.fetchall()
            self.close()
        except Exception:
            print("数据库错误")
        return list

    def insert(self,sql,params=()):
        return self.__edit(sql,params)

    def update(self, sql, params=()):
        return self.__edit(sql, params)

    def delete(self, sql, params=()):
        return self.__edit(sql, params)

    def __edit(self,sql,params):
        count=0
        try:
            self.connect()
            if type(params) == type([]):
                count = self.cursor.executemany(sql,params)
            else:
                count=self.cursor.execute(sql,params)
            self.conn.commit()
            self.close()
        except Exception:
            print("数据库错误")
        return count

#添加数据%d
def addDev(mysql):
    manyFlag = 0
    paramList = []
    params = ()
    while True:
        newID = int(input("请输入新的设备id：\n"))
        newName = input("请输入设备名字：\n")
        newUser = input("请输入设备使用者：\n")
        newPlace = input("请输入设备放置位置：\n")
        flag = input("请选择是否继续插入数据，Y（y）继续，N（n)退出\n")
        if 'Y' == flag or 'y' == flag or manyFlag>0:
            params = ([newID],newName,newUser,newPlace)
            paramList.append(params)
            manyFlag  += 1
            if 'N' == flag or 'n' == flag:
                break
        elif 'N' == flag or 'n' == flag:
            params = ([newID],newName,newUser,newPlace)
            break
        else:
            print("输入错误")
    sql = "insert into devmanage values(%s,%s,%s,%s)"
    if manyFlag:
        mysql.insert(sql,paramList)
    else:
        mysql.insert(sql,params)
#删除
def deleteDev(mysql):
    flag = int(input("请选择删除方式，1 id,2 名字，3 使用者，4放置位置\n"))
    if 1 == flag:
        deleteID = int(input("请输入删除的设备id\n"))
        sql = "delete from devmanage where id=%s"
        params = ([deleteID],)
    elif 2 == flag:
        deleteName = input("请输入要删除设备名字：\n")
        sql = "delete from devmanage where name=%s"
        params = (deleteName,)
    elif 3 == flag:
        deleteUser = input("请输入要删除设备名字：\n")
        sql = "delete from devmanage where user=%s"
        params = (deleteUser,)
    elif 4 == flag:
        deletePlace = input("请输入要删除设备名字：\n")
        sql = "delete from devmanage where place=%s"
        params = (deletePlace,)
    else:
        print("输入错误")
        return
    mysql.delete(sql,params)

#修改设备
def updateDev(mysql):
    flag = int(input("请选择查询方式，1 id,2 名字，3 使用者，4放置位置,5多值修改\n"))
    findID = int(input("输入要修改设备ID\n"))
    if 1 == flag:
        updateID = int(input("请输入新设备id:\n"))
        sql = "update devmanage set id=%s where id=%s"
        params = ([updateID],[findID])
    elif 2 == flag:
        updateName = input("请输入新设备名字:\n")
        sql = "update devmanage set name=%s where id=%s"
        params = (updateName,[findID])
    elif 3 == flag:
        updateUser = input("请输入新设备使用者:\n")
        sql = "update devmanage set user=%s where id=%s"
        params = (updateUser,[findID])
    elif 4 == flag:
        updatePlace = input("请输入新设备位置:\n")
        sql = "update devmanage set place=%s where id=%s"
        params = (updatePlace,[findID])
    elif 5 == flag:
        newID = int(input("请输入新的设备id：\n"))
        newName = input("请输入设备名字：\n")
        newUser = input("请输入设备使用者：\n")
        newPlace = input("请输入设备放置位置：\n")

    else:
        print("输入错误")
        return
    mysql.update(sql, params)

#查找
def findDev(mysql):
    flag = int(input("请选择查询方式，1 id,2 名字，3 使用者，4放置位置\n"))
    if 1 == flag:#id
        findID = int(input("请输入查询id:\n"))
        sql = "select * from devmanage where id=%s"
        params = ([findID],)
    elif 2 == flag:#名字
        findName = input("请输入查询名字:\n")
        sql = "select * from devmanage where name=%s"
        params = (findName,)
    elif 3 == flag:#使用者
        findUser = input("请输入查询使用者:\n")
        sql = "select * from devmanage where user=%s"
        params = (findUser,)
    elif 4 == flag:#放置位置
        findPlace = input("请输入查询放置位置:\n")
        sql = "select * from devmanage where place=%s"
        params = (findPlace,)
    else:
        print("输入错误")
        return
    query = mysql.get_all(sql, params)
    if query:
        for data in query:
            print(data)

def showAll(mysql):
    sql = "select * from devmanage"
    query = mysql.get_all(sql)
    for data in query:
        print(data)
def main():
    mysql = Cmysql("localhost", 3306, "devdb", "root", "root")
    while True:
        choose = int(input("选择，1添加设备，2修改设备，3删除设备，4查询设备,5所有数据，0退出\n"))
        if 1 == choose:
            addDev(mysql)
        elif 2 == choose:
            updateDev(mysql)
        elif 3 == choose:
            deleteDev(mysql)
        elif 4 == choose:
            findDev(mysql)
        elif 5== choose:
            showAll(mysql)
        elif 0 == choose:
            break
        else:
            print("输入错误")
if __name__ == '__main__':
    main()